﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Collections.ObjectModel;
using SYcwpb.Models;
using SYcwpb.Helpers;
using System.Data;

namespace SYcwpb.Access
{
    class StatisticsAccess
    {
        private int line = Convert.ToInt32(CONSTDATA.UserModel.Line);
        SqlDataLayer sqlHelper = new SqlDataLayer();

        public DataTable GetUnStatisticsDates()
        {
            string sql_get = "select TTD.ID,TTD.AssignDate,TIF.TableName"+
                            " from ReTurnRecord Rd left join TableToDate TTD on TTD.AssignDate = Rd.re_date"+
                            " left join TableInfo TIF on TTD.TableInfo_id = TIF.ID"+
                            " where TTD.MilesAdded = 0 and TIF.LineID = " + line;

            return sqlHelper.SelectDt(sql_get);
        }

        public bool CheckIsMilesAdded(string date)
        {
            string sql_check = "select MilesAdded" +
                                " from TableToDate TTD left join TableInfo TIF  on TableInfo_id = TIF.ID" +
                                " where AssignDate = '" + date + "' and TIF.LineID = " + line;

            object obj = sqlHelper.GetSingle(sql_check);
            
            return obj == null ?false:(Convert.ToInt32(obj) == 1 ? true :false);
        }

        public bool SaveAutoStaResust(DataTable autoStaResultDT,string date)
        {
            if (null == autoStaResultDT || autoStaResultDT.Rows.Count < 1)
                return false;
            ArrayList sqlList = new ArrayList();

            for (int i = 0; i < autoStaResultDT.Rows.Count; i++)
            {
                string id = autoStaResultDT.Rows[i]["ID"].ToString();
                string _class = GetIDByClassName(autoStaResultDT.Rows[i]["Group"].ToString());
                string PostName = autoStaResultDT.Rows[i]["PostName"].ToString();
                string LineID = autoStaResultDT.Rows[i]["LineID"].ToString();
                string safeMiles = autoStaResultDT.Rows[i]["SafeMiles"].ToString();
                string followMiles = autoStaResultDT.Rows[i]["FollowMiles"].ToString();
                string hours = autoStaResultDT.Rows[i]["WorkHours"].ToString();
                string sql_insert = "insert into DriverMilesRecord (dmr_user_id,dmr_post_name,dmr_date,dmr_line,dmr_cls,dmr_safemiles,dmr_followmiles,dmr_hours) values (" +
                                    "'" + id + "','" + PostName + "','" + date + "','" + LineID + "','" + _class + "','" + safeMiles + "','" + followMiles + "','" + hours + "')";
                sqlList.Add(sql_insert);
            }

            //将MilesAdded字段设为1 
            string sql_update = "update TableToDate set MilesAdded = 1" + 
                                " from TableToDate TTD left join TableInfo TIF on TableInfo_id = TIF.ID" +
                                " where AssignDate = '" + date + "' and TIF.LineID = " + line;
            sqlList.Add(sql_update);

            return sqlHelper.ExecuteSqlTran(sqlList);
        }

        private string GetIDByClassName(string _class)
        {
            string ID = "";
            switch (_class)
            {
                case "早班":
                    ID = "0";
                    break;
                case "白班":
                    ID = "2";
                    break;
                case "夜班":
                    ID = "3";
                    break;
            }
            return ID;
        }


        /// <summary>
        /// 自动统计中：返回选择日期的公里数结果
        /// </summary>
        /// <returns></returns>
        public DataTable GetSelectedDateResult(string date)
        {
            string sql_get = "select" +
                            " [user_id] as ID," + 
                            " [user_name] as UserID," +   //司机工号
                            " user_view_name  as [Name]," +   //司机姓名
                            " dar_cls_id as [Group]," +
                            " dar_line as LineID," +
                            " dar_post_name as PostName," + 
                            " stringRunTrain as CrossingRoad," +
                            " dar_grp_type as GrpType," +
                            " miles as Miles," +
                            " wHours as WorkHours," +
                            " dar_cr_id as CrossRoadID," + 
                            " reptrain," +
                            " attenPlace," +
                            " offPlace " +
                            " from DriverAttendRecord Dar left join [system_user] S on dar_user_id = user_id" +
                            " left join CrossingRoad Cr on dar_cr_id = Cr.ID" +
                            " where dar_line = " + line + " and dar_dgrp_date = '" + date + "'" +
                            " order by [Group],attenTime " ;

            return sqlHelper.SelectDt(sql_get);
        }

        /// <summary>
        /// 手动输入：返回选择日期的公里数、工时信息
        /// </summary>
        /// <param name="?"></param>
        /// <returns></returns>
        public DataTable Manaual_SelectDateResult(string Date)
        {
            string sql_get = "select ID," + 
                            " [user_name] as UserID," +
                            " [user_view_name] as [Name]," +
                            " [user_line] as LineID," +
                            " dmr_post_name as PostName," + 
                            " dmr_cls as [Group]," +
                            " dmr_date as Date," +
                            " dmr_safemiles as SafeMiles," +
                            " dmr_followmiles as FollowMiles," +
                            " dmr_hours as WorkHours" +
                            " from DriverMilesRecord left join [system_user] on dmr_user_id = user_id " +
                            " where user_line = " + line + " and dmr_date = '" + Date + "' order by dmr_cls,user_name";

            return sqlHelper.SelectDt(sql_get);
        }

        public bool SaveManaualEditChange(DataRow row)
        {
            string ID = row["ID"].ToString();
            if (string.IsNullOrEmpty(ID))
                return false;

            string SafeMiles = row["SafeMiles"].ToString();
            string FollowMiles = row["FollowMiles"].ToString();
            string Hours = row["WorkHours"].ToString();

            string sql_update = "update DriverMilesRecord set dmr_safemiles = '" + SafeMiles +"',dmr_followmiles = '" + FollowMiles + "', dmr_hours = '" + Hours + "' where ID = '" + ID + "'";
            return sqlHelper.ExecuteSql(sql_update) > 0;
        }

        /// <summary>
        /// 插入新的一行
        /// </summary>
        /// <returns></returns>
        public bool ManaualAddNew(SingleDriverModel driver,string date ,string cls, string miles,string hours,ref int newID)
        {
            string sql_insert = "insert into DriverMilesRecord (dmr_user_id,dmr_post_name,dmr_date,dmr_line,dmr_cls,dmr_safemiles,dmr_followmiles,dmr_hours) values('" + 
                                  driver.UserID + "','" + driver.PostName + "','" + date + "','" + driver.Line + "','" + cls + "',";
            if (driver.PostName == "电客车司机")
                sql_insert += "'" + miles + "',0,'" + hours + "')";
            else
                sql_insert = "0,'" + miles + "','" + hours + "')";

            sql_insert += ";select @@IDENTITY as ID;";

            object obj = sqlHelper.GetSingle(sql_insert);
            if (obj == null)
                return false;

            newID = Convert.ToInt32(obj);
            return true;
        }

        public bool DeleteManaualRow(DataRow row)
        {
            string ID = row["ID"].ToString();
            if (string.IsNullOrEmpty(ID))
                return false;

            string sql_delete = "delete from DriverMilesRecord where ID = '" + ID + "'";
            return sqlHelper.ExecuteSql(sql_delete) > 0;
        }

        public DataTable GetDriversCollections()
        {
            string sql_getDriver = "select user_id, user_name,user_view_name,user_line,[Post].post_name " + 
                                   " from [system_user] S left join Position [Post] on user_post = [Post].ID" +
                                   " where user_dep_id =7 and user_line = " + line + " order by user_name";

            return sqlHelper.SelectDt(sql_getDriver);
        }

        /// <summary>
        /// 返回电客车司机Table 时type =1 ,返回非电客车司机 时type = 0
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="days"></param>
        /// <returns></returns>
        public DataTable GetViewMilesDT(DateTime startDate,int days,int type)
        {
            string sql_query = "select distinct(dmr_user_id) as ID," +
                                "user_name as 司机工号," +
                                "user_view_name as 司机姓名," +
                                "0.00 as 总里程," +
                                "0.00 as 计价,";
            for (int i = 0; i <= days; i++)
            {
                sql_query += "0.00 as '" + startDate.AddDays(i).ToString("MM-dd") + "',";
            }
            //去掉最后一个逗号
            sql_query = sql_query.Substring(0, sql_query.Length - 1);
            if (type == 1)
                sql_query += " from DriverMilesRecord left join [system_user] on dmr_user_id = user_id where dmr_post_name = '电客车司机' and dmr_line = '" + line + "'";
            else
                sql_query += " from DriverMilesRecord left join [system_user] on dmr_user_id = user_id where dmr_post_name <> '电客车司机' and dmr_line = '" + line + "'";

            return sqlHelper.SelectDt(sql_query);
        }

        public DataTable GetViewHourDT(DateTime startDate, int days)
        {
            string sql_query = "select distinct(dmr_user_id) as ID," +
                                "user_name as 司机工号," +
                                "user_view_name as 司机姓名," +
                                "0.00 as 总工时," +
                                "0.00 as 计价,";
            for (int i = 0; i <= days; i++)
            {
                sql_query += "0.00 as '" + startDate.AddDays(i).ToString("MM-dd") + "',";
            }
            //去掉最后一个逗号
            sql_query = sql_query.Substring(0, sql_query.Length - 1);
            sql_query += " from DriverMilesRecord left join [system_user] on dmr_user_id = user_id where dmr_line = '" + line + "'";

            return sqlHelper.SelectDt(sql_query);
        }

        public DataTable getSafeMilesRecordInDays(string startDate ,string endDate)
        {
            string sql_query = "select dmr_user_id as UserID,dmr_date as date,dmr_safemiles as miles from DriverMilesRecord where dmr_post_name = '电客车司机' and dmr_line =" + line + " and dmr_date >= '" + startDate + "' and dmr_date <= '" + endDate + "'";
            return sqlHelper.SelectDt(sql_query);
        }

        public DataTable getfollowMilesRecordInDays(string startDate, string endDate)
        {
            string sql_query = "select dmr_user_id as UserID,dmr_date as date,dmr_followmiles as miles from DriverMilesRecord where dmr_post_name <> '电客车司机' and dmr_line =" + line + " and dmr_date >= '" + startDate + "' and dmr_date <= '" + endDate + "'";
            return sqlHelper.SelectDt(sql_query);
        }

        public DataTable GetHoursRecordInDays(string startDate, string endDate)
        {
            string sql_query = "select dmr_user_id as UserID,dmr_date as date,dmr_hours as hour from DriverMilesRecord where dmr_line =" + line + " and dmr_date >= '" + startDate + "' and dmr_date <= '" + endDate + "'";
            return sqlHelper.SelectDt(sql_query);
        }

        public DataTable GetParaMeters()
        {
            string sql_get = "select * from StatisticsPara where Line = " + line; 

            return sqlHelper.SelectDt(sql_get);
        }

        public bool UpdateParaMeters(string inMiles,
                                    string outMiles, 
                                    string upToChangedMiles, 
                                    string downToChangedMiles,
                                    string totalMiles,
                                    string milePrice,
                                    string hourPrice
            )
        {
            string sql_update = "update StatisticsPara set InMiles = '" + inMiles + "',OutMiles = '" + outMiles + "',UpToChangedMiles ='" +
                upToChangedMiles + "',DownToChangedMiles = '" + downToChangedMiles + "',TotalMiles = '" + totalMiles + "',MilePrice = '" +
                milePrice + "',HourPrice = '" + hourPrice + "' where Line = " + line;

            return sqlHelper.ExecuteSql(sql_update) > 0;
        }
    }
}
